/*
 * @(#)AbstractDAObject.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.framework.dao;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;
import java.util.Vector;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.impl.LogFactoryImpl;

import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.IDFactory;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.SystemParameterConstant;
import com.dcivision.framework.SystemParameterFactory;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;

/**
  AbstractDAObject.java

  The superest class of the Data Access Object to perform the database operation.

    @author          Rollo Chan
    @company         DCIVision Limited
    @creation date   08/07/2003
    @version         $Revision: 1.85.2.2 $
*/

public abstract class AbstractDAObject implements java.io.Serializable {
  private static final String ESCAPE_CHAR = "/";
  // Private member variables.
  protected Log log = new LogFactoryImpl().getInstance(this.getClass());
  protected SessionContainer sessionContainer = null;
  protected AbstractBaseObject oldValue = null;
  protected boolean isSoftDeleteEnabled = false;

  // Fields for DB operation.
  protected Connection dbConn = null;
  protected String baseTableName = null;
  protected Vector vecDBColumn = null;

  public AbstractDAObject() {
    this.isSoftDeleteEnabled = new Boolean(SystemParameterFactory.getSystemParameter(SystemParameterConstant.SOFT_DELETE_ENABLE)).booleanValue();
    this.vecDBColumn = new Vector();
    initDBSetting();
  }

  public AbstractDAObject(Connection dbConn) {
    this();
    this.sessionContainer = new SessionContainer();
    this.dbConn = dbConn;
  }

  public AbstractDAObject(SessionContainer sessionContainer, Connection dbConn) {
    this();
    this.sessionContainer = sessionContainer;
    this.dbConn = dbConn;
  }

  public void setSessionContainer(SessionContainer sessionContainer) {
    this.sessionContainer = sessionContainer;
  }

  public SessionContainer getSessionContainer() {
    return this.sessionContainer;
  }

  public void setDbConn(Connection dbConn) {
    this.dbConn = dbConn;
  }

  public Connection getDbConn() {
    return this.dbConn;
  }

  public synchronized AbstractBaseObject getObjectByID(Integer id) throws ApplicationException {
    return(this.getByID(id));
  }

  public synchronized List getFullList() throws ApplicationException {
    return(this.getList());
  }

  public synchronized List getFullList(com.dcivision.framework.web.AbstractSearchForm searchForm) throws ApplicationException {
    return(this.getList(searchForm));
  }

  public synchronized AbstractBaseObject insertObject(AbstractBaseObject obj) throws ApplicationException {
    this.validateInsert(obj);
    AbstractBaseObject result = this.insert(obj);
    this.postInsert(obj.getID());
    this.auditTrail(GlobalConstant.OP_MODE_INSERT, obj);
    return(result);
  }

  public synchronized AbstractBaseObject updateObject(AbstractBaseObject obj) throws ApplicationException {
    oldValue = this.getByID(obj.getID());
    this.validateUpdate(obj);
    AbstractBaseObject result = this.update(obj);
    this.postUpdate(obj.getID());
    this.auditTrail(GlobalConstant.OP_MODE_UPDATE, obj);
    return(result);
  }

  public synchronized AbstractBaseObject deleteObject(AbstractBaseObject obj) throws ApplicationException {
    this.validateDelete(obj);
    oldValue = this.getByID(obj.getID());
    AbstractBaseObject result = obj;
    if (this.isSoftDeleteEnabled) {
      this.softDelete(obj.getID());
    } else {
      result = this.delete(obj);
    }
    this.postDelete(obj.getID());
    this.auditTrail(GlobalConstant.OP_MODE_DELETE, obj);
    return(result);
  }

  public synchronized void deleteList(String[] idAry) throws ApplicationException {
    if (this.isSoftDeleteEnabled) {
      this.softDeleteList(idAry);
    } else {
      this.hardDeleteList(idAry);
    }

    if (!Utility.isEmpty(idAry)) {
      for (int i = 0; i < idAry.length; i++) {
        this.postDelete(new Integer(idAry[i]));
      }
    }
  }

  public synchronized AbstractBaseObject deleteObjectByID(Integer id) throws ApplicationException {
    AbstractBaseObject obj = this.getByID(id);
    AbstractBaseObject result = this.deleteObject(obj);
    return(result);
  }

  protected void auditTrailBase(String opMode, Vector oldValues, Vector newValues) throws ApplicationException {
    /********************************************************
     * TODO: Update database for the audit trail entries.
     ********************************************************/
  }

  public String getTableName() {
    return(this.baseTableName);
  }

  // Protected methods for preparing statment for DB operation with NULL values.
  public void setPrepareStatement(java.sql.PreparedStatement preStat, int idx, Integer val) throws java.sql.SQLException {
    if (val==null) {
      preStat.setNull(idx, java.sql.Types.NUMERIC);
    } else {
      preStat.setInt(idx, val.intValue());
    }
  }
  
  public void setPrepareStatement(PreparedStatement preStat, int idx, Long val) throws SQLException {
    if (val == null) {
      preStat.setNull(idx, java.sql.Types.NUMERIC);
    } else {
      preStat.setLong(idx, val.longValue());
    }
  }

  public void setPrepareStatement(java.sql.PreparedStatement preStat, int idx, Float val) throws java.sql.SQLException {
    if (val==null) {
      preStat.setNull(idx, java.sql.Types.NUMERIC);
    } else {
      preStat.setFloat(idx, val.floatValue());
    }
  }

  public void setPrepareStatement(java.sql.PreparedStatement preStat, int idx, Double val) throws java.sql.SQLException {
    if (val==null) {
      preStat.setNull(idx, java.sql.Types.NUMERIC);
    } else {
      preStat.setDouble(idx, val.doubleValue());
    }
  }

  public void setPrepareStatement(java.sql.PreparedStatement preStat, int idx, java.sql.Date val) throws java.sql.SQLException {
    if (val==null) {
      preStat.setNull(idx, java.sql.Types.DATE);
    } else {
      preStat.setDate(idx, val);
    }
  }

  public void setPrepareStatement(java.sql.PreparedStatement preStat, int idx, java.sql.Timestamp val) throws java.sql.SQLException {
    if (val==null) {
      preStat.setNull(idx, java.sql.Types.TIMESTAMP);
    } else {
      // Due to the ABNORMAL behavior of the JDBC-ODBC bridge, the
      // Timestamp cannot be updated correctly. The "second" of timestamp
      // will be truncated. Thus, need to use "setString()" instead of
      // "setTimestamp()"
      // preStat.setString(idx, Utility.formatDate(val, "yyyy-MM-dd HH:mm:ss"));
      preStat.setTimestamp(idx, val);
    }
  }

  public void setPrepareStatement(java.sql.PreparedStatement preStat, int idx, String val) throws java.sql.SQLException {
    if (val==null) {
      preStat.setNull(idx, java.sql.Types.VARCHAR);
    } else {
      preStat.setString(idx, TextUtility.noNull(val).trim());
    }
  }

  public void setPrepareStatement(java.sql.PreparedStatement preStat, int idx, Object val) throws java.sql.SQLException {
    if (val==null) {
      preStat.setNull(idx, java.sql.Types.BINARY);
    } else {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      baos = new ByteArrayOutputStream();
      try {
        ObjectOutputStream out = new ObjectOutputStream(baos);
        out.writeObject(val);
        out.flush();
      } catch (java.io.IOException ioe) {
        log.error("Cannot write object stream", ioe);
        new java.sql.SQLException("Cannot write object stream");
      }
      preStat.setBytes(idx, baos.toByteArray());
    }
  }

  /**
   * Add by TC for SubQuery
   * @param preStat
   * @param idx
   * @param val
   * @param type
   * @throws SQLException
   */
  public void setPrepareStatement(java.sql.PreparedStatement preStat, int idx, Object val,String type) throws SQLException{
    if(type.equals(Integer.class.getName())) {
      this.setPrepareStatement(preStat, idx, (Integer)val);
    }
    if(Float.class.getName().equals(type)) {
      this.setPrepareStatement(preStat, idx, (Float)val);
    }
    if(Double.class.getName().equals(type)) {
      this.setPrepareStatement(preStat, idx, (Double)val);
    }
    if(java.sql.Date.class.getName().equals(type)) {
      this.setPrepareStatement(preStat, idx, (java.sql.Date)val);
    }
    if(java.sql.Timestamp.class.getName().equals(type)) {
      this.setPrepareStatement(preStat, idx, (java.sql.Timestamp)val);
    }
    if(String.class.getName().equals(type)) {
      this.setPrepareStatement(preStat, idx, (String)val);
    }
    
  }
  
  public int setPreparedStatement ( PreparedStatement stat, int startIndex, List params ) throws SQLException{
    for(Iterator it=params.iterator();it.hasNext();){
      Object obj = it.next();
      setPrepareStatement(stat,startIndex++,obj,obj.getClass().getName());
    }
    return startIndex;
  }
  /**
   *  call setPrepareStatment by a list of Interger[]
   *  @return the next index number
   */
  public int setPrepareStatement(java.sql.PreparedStatement preStat, int idx, Integer[] array) throws java.sql.SQLException{
    if (array!=null && array.length>0) {
      for (int j=0; j<array.length; j++) {
        this.setPrepareStatement(preStat, idx++, array[j]);
      }
    }
    return idx;
  }

  /**
   *  call setPrepareStatment by a list of String[]
   *  @return the next index number
   */
  public int setPrepareStatement(java.sql.PreparedStatement preStat, int idx, String[] array) throws java.sql.SQLException{
    if (array!=null && array.length>0) {
      for (int j=0; j<array.length; j++) {
        this.setPrepareStatement(preStat, idx++, array[j]);
      }
    }
    return idx;
  }

  /**
   *  call setPrepareStatment by a list of Float[]
   *  @return the next index number
   */
  public int setPrepareStatement(java.sql.PreparedStatement preStat, int idx, Float[] array) throws java.sql.SQLException{
    if (array!=null && array.length>0) {
      for (int j=0; j<array.length; j++) {
        this.setPrepareStatement(preStat, idx++, array[j]);
      }
    }
    return idx;
  }

  /**
   *  call setPrepareStatment by a list of Double[]
   *  @return the next index number
   */
  public int setPrepareStatement(java.sql.PreparedStatement preStat, int idx, Double[] array) throws java.sql.SQLException{
    if (array!=null && array.length>0) {
      for (int j=0; j<array.length; j++) {
        this.setPrepareStatement(preStat, idx++, array[j]);
      }
    }
    return idx;
  }

  // Protected methods for getting values from resultset.
  protected Integer getResultSetInteger(java.sql.ResultSet rs, int idx) throws java.sql.SQLException {
    int result = rs.getInt(idx);
    if (rs.wasNull()) {
      return(null);
    } else {
      return(new Integer(result));
    }
  }

  protected Double getResultSetDouble(java.sql.ResultSet rs, int idx) throws java.sql.SQLException {
    double result = rs.getDouble(idx);
    if (rs.wasNull()) {
      return(null);
    } else {
      return(new Double(result));
    }
  }

  protected Float getResultSetFloat(java.sql.ResultSet rs, int idx) throws java.sql.SQLException {
    float result = rs.getFloat(idx);
    if (rs.wasNull()) {
      return(null);
    } else {
      return(new Float(result));
    }
  }

  protected java.sql.Date getResultSetDate(java.sql.ResultSet rs, int idx) throws Exception {
    return(rs.getDate(idx));
  }

  protected java.sql.Timestamp getResultSetTimestamp(java.sql.ResultSet rs, int idx) throws Exception {
    return(rs.getTimestamp(idx));
  }

  protected String getResultSetString(java.sql.ResultSet rs, int idx) throws Exception {
    return(rs.getString(idx));
  }

  protected Object getResultSetObject(java.sql.ResultSet rs, int idx) throws Exception {
    InputStream inputStream = rs.getBinaryStream(idx);
    ObjectInputStream in = new ObjectInputStream(inputStream);
    Object result = in.readObject();
    in.close();
    return(result);
  }

  protected Integer getResultSetInteger(java.sql.ResultSet rs, String colName) throws Exception {
    int result = rs.getInt(colName);
    if (rs.wasNull()) {
      return(null);
    } else {
      return(new Integer(result));
    }
  }
  
  protected Long getResultSetLong(java.sql.ResultSet rs, String colName) throws Exception {
    long result = rs.getLong(colName);
    if (rs.wasNull()) {
      return(null);
    } else {
      return(new Long(result));
    }
  }

  protected Double getResultSetDouble(java.sql.ResultSet rs, String colName) throws Exception {
    double result = rs.getDouble(colName);
    if (rs.wasNull()) {
      return(null);
    } else {
      return(new Double(result));
    }
  }

  protected Float getResultSetFloat(java.sql.ResultSet rs, String colName) throws Exception {
    float result = rs.getFloat(colName);
    if (rs.wasNull()) {
      return(null);
    } else {
      return(new Float(result));
    }
  }

  protected java.sql.Date getResultSetDate(java.sql.ResultSet rs, String colName) throws Exception {
    return(rs.getDate(colName));
  }

  protected java.sql.Timestamp getResultSetTimestamp(java.sql.ResultSet rs, String colName) throws Exception {
    return(rs.getTimestamp(colName));
  }

  protected String getResultSetString(java.sql.ResultSet rs, String colName) throws Exception {
    return(rs.getString(colName));
  }

  protected String[] getResultSetStringAry(java.sql.ResultSet rs, String colName, String delims) throws Exception {
    List resultList = new ArrayList();
    String targetStr = rs.getString(colName);
    if (!Utility.isEmpty(targetStr)){
      return TextUtility.splitString(targetStr, delims);
    }else{
      return (new String[0]);
    }
  }

  protected Object getResultSetObject(java.sql.ResultSet rs, String colName) throws Exception {
    InputStream inputStream = rs.getBinaryStream(colName);
    ObjectInputStream in = new ObjectInputStream(inputStream);
    Object result = in.readObject();
    in.close();
    return(result);
  }

  protected String toAuditTrailValue(Object val) {
    if (val==null) {
      return(GlobalConstant.AUDIT_TRAIL_NULL);
    } else {
      if (val instanceof Timestamp) {
        return(TextUtility.formatDate((Timestamp)val, GlobalConstant.AUDIT_TRAIL_DATE_FORMAT));
      } else {
        return(val.toString());
      }
    }
  }

  public void restore(Integer id) throws ApplicationException {
    String[] inputAry = new String[] { id.toString() };
    restore(inputAry);
  }

  public void restore(String[] idAry) throws ApplicationException {
    this.updateStatus(idAry, GlobalConstant.RECORD_STATUS_ACTIVE);
  }

  protected void softDelete(Integer id) throws ApplicationException {
    String[] inputAry = new String[] { id.toString() };
    softDeleteList(inputAry);
  }

  protected void softDeleteList(String[] idAry) throws ApplicationException {
    this.updateStatus(idAry, GlobalConstant.RECORD_STATUS_INACTIVE);
  }

  protected void hardDeleteList(String[] idAry) throws ApplicationException {
    if (idAry==null || idAry.length==0) {
      return;
    }

    StringBuffer sqlStat = new StringBuffer("DELETE FROM " + this.baseTableName + " WHERE ID=" + idAry[0]);
    for (int i = 1; i < idAry.length; i++) {
      sqlStat.append(" OR ID=" + idAry[i]);
    }

    PreparedStatement preStat = null;

    synchronized(dbConn) {
      try {
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        preStat.executeUpdate();
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, e, e.toString());
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  private void updateStatus(String[] idAry, String status) throws ApplicationException {
    if (idAry==null || idAry.length==0) {
      return;
    }

    StringBuffer sqlStat = new StringBuffer("UPDATE " + this.baseTableName + " SET RECORD_STATUS='" + status + "', UPDATE_DATE=?, UPDATER_ID=? WHERE ID=" + idAry[0]);
    for (int i = 1; i < idAry.length; i++) {
      sqlStat.append(" OR ID=" + idAry[i]);
    }

    PreparedStatement preStat = null;

    synchronized(dbConn) {
      try {
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, Utility.getCurrentTimestamp());
        this.setPrepareStatement(preStat, 2, (sessionContainer==null || sessionContainer.getUserRecordID()==null) ? new Integer(0) : sessionContainer.getUserRecordID());
        log.debug("sqlStat="+sqlStat.toString());//simon_add
        preStat.executeUpdate();
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, e, e.toString());
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected String composeORSubSQL(Object[] criteriaArray, String colName, String prefix, String suffix) {
    StringBuffer sqlCriteria = new StringBuffer();
    if (!Utility.isEmpty(criteriaArray)) {
      sqlCriteria.append(" ").append(prefix).append(" (");
      for (int i=0; i<criteriaArray.length; i++) {
        sqlCriteria.append(" ").append(colName).append("=? OR");
      }
      sqlCriteria.delete(sqlCriteria.length()-2, sqlCriteria.length());
      sqlCriteria.append(") ").append(suffix);
    }
    return sqlCriteria.toString();
  }

  protected String composeAndSubSQL(Object[] criteriaArray, String colName, String prefix, String suffix) {
    StringBuffer sqlCriteria = new StringBuffer();
    if (!Utility.isEmpty(criteriaArray)) {
      sqlCriteria.append(" ").append(prefix).append(" (");
      for (int i=0; i<criteriaArray.length; i++) {
        sqlCriteria.append(" ").append(colName).append("=? AND");
      }
      sqlCriteria.delete(sqlCriteria.length()-2, sqlCriteria.length());
      sqlCriteria.append(") ").append(suffix);
    }
    return sqlCriteria.toString();
  }

  protected Integer getNextPrimaryID() throws ApplicationException {
    return IDFactory.getInstance().getNextSequence(this.baseTableName);
  }  

  protected void positionCursor(ResultSet rs, int curStartRowNo, int pageOffset) throws SQLException {
    int totalOffset = curStartRowNo - 1;

    switch (DataSourceFactory.getDatabaseType()) {
      case DataSourceFactory.DB_MYSQL:
        // Do Nothing.
      break;
      case DataSourceFactory.DB_INTERBASE:
        // Do Nothing.
      break;
      case DataSourceFactory.DB_POSTGRESQL:
        // Do Nothing.
      break;
      case DataSourceFactory.DB_ORACLE:
        // Do Nothing.
      break;
      case DataSourceFactory.DB_MSSQL:
        // Loop to that particular position.
        if (totalOffset > 0) {
          rs.absolute(totalOffset);
        }
      break;
      case DataSourceFactory.DB_SYBASE:
        // Loop to that particular position.
        if (totalOffset > 0) {
          rs.absolute(totalOffset);
        }
      break;
      case DataSourceFactory.DB_DB2:
        // Do Nothing.
      break;
    }
  }

  protected StringBuffer getSelectListSQL(StringBuffer sbSQL, int startOffset, int pageSize) {
    StringBuffer sbResult = new StringBuffer(sbSQL.toString());

    switch (DataSourceFactory.getDatabaseType()) {
      case DataSourceFactory.DB_MYSQL:
        // select * from tablea LIMIT 50, 10
        sbResult.append(" LIMIT " + (startOffset - 1) + ", " + pageSize + " ");
      break;
      case DataSourceFactory.DB_INTERBASE:
        // select * from tablea ROWS 51 TO 60
        sbResult.append(" ROWS " + startOffset + " TO " + (startOffset + pageSize - 1) + " ");
      break;
      case DataSourceFactory.DB_POSTGRESQL:
        // select * from tablea LIMIT 50 OFFSET 60
        sbResult.append(" LIMIT " + (startOffset - 1) + " OFFSET " + pageSize + " ");
      break;
      case DataSourceFactory.DB_ORACLE:
        // SELECT * FROM (select ROWNUM AS DCI_ROW, * from tablea) WHERE DCI_ROW BETWEEN 51 AND 60
        sbResult = new StringBuffer("SELECT * FROM (SELECT ROWNUM AS DCI_ROW, DCI_TABLE.* FROM (").append(sbResult);
        sbResult.append(") DCI_TABLE ) WHERE DCI_ROW BETWEEN " + (startOffset) + " AND " + (startOffset + pageSize - 1));
      break;
      case DataSourceFactory.DB_MSSQL:
        // SELECT TOP 60 * from table a
        if (sbResult.toString().toUpperCase().indexOf("DISTINCT") > -1) {
          sbResult = sbResult.insert(sbResult.toString().toUpperCase().indexOf("DISTINCT") + 8, " TOP " + (startOffset + pageSize - 1) + " ");
        } else {
          sbResult = sbResult.insert(sbResult.toString().toUpperCase().indexOf("SELECT") + 6, " TOP " + (startOffset + pageSize - 1) + " ");
        }
      break;
      case DataSourceFactory.DB_SYBASE:
        // Do Nothing.
      break;
      case DataSourceFactory.DB_DB2:
        // SELECT * FROM (select row_number() over() AS DCI_ROW, * from tablea) WHERE DCI_ROW BETWEEN 50 AND 61
        sbResult = new StringBuffer("SELECT * FROM (SELECT row_number() over() AS DCI_ROW,").append(sbResult.substring(6));
        sbResult.append(") WHERE DCI_ROW BETWEEN " + (startOffset - 1) + " AND " + (startOffset + pageSize));
      break;
    }

    //log.info("SELECT SQL: " + sbResult);
    return(sbResult);
  }
  
  /**
   * Format the SQLstring when it contains 'union' substring and 'order by' subString at the same time 
   * @param sbSQL
   * @param sortAttribute
   * @param sortOrder
   * @return
   */
  protected StringBuffer getUnionOrderSQL(StringBuffer sbSQL,String sortAttribute,String sortOrder){
    // Form the ORDER clause for sorting.
	  StringBuffer sbResult = new StringBuffer(sbSQL.toString());
	  String sortAttributeStr = null;
    if(sortAttribute.indexOf(".") >= 0) {
      sortAttributeStr = sortAttribute.substring(sortAttribute.indexOf(".")+1);
    } else {
      sortAttributeStr = sortAttribute;
    } 
	  
	  switch (DataSourceFactory.getDatabaseType()) {
	  case DataSourceFactory.DB_MYSQL:
	      
	      sbResult = replaceSQL(sbResult, "union", ")\nUNION\n("); 
	      sbResult.insert(0, "(");
	      sbResult.append(") ");
	      sbResult.append("ORDER BY ");
	      sbResult.append(sortAttributeStr);
	      sbResult.append(" ");
	      sbResult.append(sortOrder);
	      break;
	      case DataSourceFactory.DB_INTERBASE:
	        // Do Nothing
	    	  sbResult.append("ORDER BY ");
	    	  sbResult.append(sortAttribute);
	    	  sbResult.append(" ");
	    	  sbResult.append(sortOrder);
	      break;
	      case DataSourceFactory.DB_POSTGRESQL:
	        // Do Nothing
	    	  sbResult.append("ORDER BY ");
	    	  sbResult.append(sortAttribute);
	    	  sbResult.append(" ");
	    	  sbResult.append(sortOrder);
	      break;
	      case DataSourceFactory.DB_ORACLE:
	        // 
	    	  sbResult.insert(0, "SELECT * FROM (");
	    	  sbResult.append(") DCI_UNI_TABLE ");
	    	  sbResult.append("ORDER BY ");
	    	  sbResult.append(sortAttributeStr);
	    	  sbResult.append(" ");
	    	  sbResult.append(sortOrder);
	      break;
	      case DataSourceFactory.DB_MSSQL:
	        // Do Nothing
          sbResult.insert(0, "SELECT * FROM (");
          sbResult.append(") DCI_UNI_TABLE ");
          sbResult.append("ORDER BY ");
          sbResult.append(sortAttributeStr);
          sbResult.append(" ");
          sbResult.append(sortOrder);
	      break;
	      case DataSourceFactory.DB_SYBASE:
	        // Do Nothing.
	    	  sbResult.append("ORDER BY ");
	    	  sbResult.append(sortAttribute);
	    	  sbResult.append(" ");
	    	  sbResult.append(sortOrder);
	      break;
	      case DataSourceFactory.DB_DB2:
	        // Do Nothing
	    	  sbResult.append("ORDER BY ");
	    	  sbResult.append(sortAttribute);
	    	  sbResult.append(" ");
	    	  sbResult.append(sortOrder);
	      break;
	  }
	  log.debug("SELECT SQL: " + sbResult);
	  return (sbResult);
  }
  
  /**
   * replace the substring in the SQLstring with the currect String
   * @param sbSQL
   * @param regex
   * @param replacement
   * @return
   */
  protected  StringBuffer replaceSQL(StringBuffer sbSQL,String regex,String replacement){
    String regexUpcase = regex.toUpperCase();
    String sqlStr = sbSQL.toString().toUpperCase();

    sqlStr = sqlStr.replaceAll((" "+regexUpcase+" "), ("\n"+regexUpcase+"\n"));
    sqlStr = sqlStr.replaceAll(("\n"+regexUpcase+" "), ("\n"+regexUpcase+"\n"));
    sqlStr = sqlStr.replaceAll((" "+regexUpcase+"\n"), ("\n"+regexUpcase+"\n"));
    sqlStr = sqlStr.replaceAll(("\n"+regexUpcase+"\n"),replacement);
    return new StringBuffer(sqlStr);
  }
  
/*
  protected StringBuffer delSelectListSQL(StringBuffer sbSQL, int startOffset, int pageSize) {
    StringBuffer sbResult = new StringBuffer(sbSQL.toString());

    switch (DataSourceFactory.getDatabaseType()) {
      case DataSourceFactory.DB_MYSQL:
        // select * from tablea LIMIT 50, 10
        sbResult = sbResult.delete(sbResult.indexOf("LIMIT"), sbResult.length());
      break;
      case DataSourceFactory.DB_INTERBASE:
        // select * from tablea ROWS 51 TO 60
        sbResult = sbResult.delete(sbResult.indexOf("ROWS"), sbResult.length());
      break;
      case DataSourceFactory.DB_POSTGRESQL:
        // select * from tablea LIMIT 50 OFFSET 60
        sbResult = sbResult.delete(sbResult.indexOf("LIMIT"), sbResult.length());
      break;
      case DataSourceFactory.DB_ORACLE:
        // SELECT * FROM (select ROWNUM AS DCI_ROW, * from tablea) WHERE DCI_ROW BETWEEN 50 AND 61
      break;
      case DataSourceFactory.DB_MSSQL:
        // Do Nothing.
      break;
      case DataSourceFactory.DB_SYBASE:
        // Do Nothing.
      break;
      case DataSourceFactory.DB_DB2:
        // SELECT * FROM (select row_number() over() AS DCI_ROW, * from tablea) WHERE DCI_ROW BETWEEN 50 AND 61
      break;
    }
    return(sbResult);
  }
*/

  protected HashMap getCriteriaCombinationOracle(String sFieldName, String sFieldValue, boolean bExactMatch) {
    String fieldName = "UPPER("+sFieldName+")";
    String fieldValue = "";
    if(sFieldValue!=null){
      fieldValue=sFieldValue.toUpperCase();
    }
    return getCriteriaCombinationMySQL(fieldName,fieldValue,bExactMatch);

  }

  protected HashMap getCriteriaCombinationMySQL(String sFieldName, String sFieldValue, boolean bExactMatch) {
    HashMap mResult = new HashMap();
    List fieldResult = new ArrayList();
    List criteria = new ArrayList();
    String sResult = " ( ";

    boolean bOR = false;
    boolean bAND = false;
    String bLIKEPRO = "";
    boolean bRangeSearch = false;
    String delim = "";
    int exists = 0;

    StringTokenizer st = new StringTokenizer(sFieldValue);
    String tmpCriteria = "";
    while (st.hasMoreTokens()) {
      String str = st.nextToken().trim();

      if ("OR".equals(str)) {
        bOR = true;
        delim = "OR";
        criteria.add(new String(tmpCriteria));
        tmpCriteria = "";
        str="";
      }
      else if ("AND".equals(str)) {
        bAND = true;
        delim = "AND";
        criteria.add(new String(tmpCriteria));
        tmpCriteria = "";
        str="";
      }
      else if (">".equals(str)) {
        bRangeSearch = true;
        delim = ">";
        criteria.add(new String(tmpCriteria));
        tmpCriteria = "";
        str="";
      }
      else if (">=".equals(str)) {
        bRangeSearch = true;
        delim = ">=";
        criteria.add(new String(tmpCriteria));
        tmpCriteria = "";
        str="";
      }
      if (!Utility.isEmpty(str)) {
        tmpCriteria = tmpCriteria + " " + str;
      }

    }
    if (!Utility.isEmpty(tmpCriteria)) {
      criteria.add(new String(tmpCriteria));
    }
    log.debug("delim = " + delim);

    //For range search
    if (bRangeSearch == true){
      String minValue = "";
      String maxValue = "";
      //while (st.hasMoreTokens()) {
      for (int i = 0; i<criteria.size(); i ++) {
        if (Utility.isEmpty(minValue)){
          minValue = getExactMatchKeyword((String)criteria.get(i), bExactMatch);
          minValue = minValue.replaceFirst("%", "");
          fieldResult.add(minValue);
        }else{
          maxValue = getExactMatchKeyword((String)criteria.get(i), bExactMatch);
          maxValue = maxValue.replaceFirst("%", "");
          fieldResult.add(maxValue);
        }
        exists++;
      }
      if (">".equals(delim)) {
        sResult = sResult + sFieldName + " > ? AND " + sFieldName + " < ? ";
      }
      else if (">=".equals(delim)) {
        sResult = sResult + sFieldName + " >= ? AND " + sFieldName + " <= ? ";
      }
    }//No Range search, only the comnibation of AND/OR
    else if (bAND==true || bOR ==true){
      //while (st.hasMoreTokens()) {
      for (int i = 0; i<criteria.size(); i ++) {
        String value = getExactMatchKeyword((String)criteria.get(i), bExactMatch);
        value=TextUtility.replaceString(value,"*","%");
        log.debug("value is:"+value);

        String tmpStr = "";
        String sEqual = "=";
        if (value.indexOf("NOT") >= 0) {
          bLIKEPRO=" NOT LIKE ";
          value = value.replaceAll("NOT ", "").trim();
          value = value.replaceAll("%", "").trim();
          sEqual = "!=";
        }else{
          bLIKEPRO=" LIKE ";
        }
        fieldResult.add(value);

        if (bExactMatch == false) {
          tmpStr = sFieldName + bLIKEPRO + " ? ";
        }else{
          tmpStr = sFieldName + " " + sEqual + " ? ";
        }

        if (i!=0) {
          tmpStr =  " " + delim + " " + tmpStr;
        }


        sResult = sResult + tmpStr;
        exists++;
      }
    }//One keyword only
    else {

      String value = getExactMatchKeyword(tmpCriteria, bExactMatch);
      String tmpStr = "";
      String sEquals="=";
      if (value.indexOf("NOT") >= 0) {
        bLIKEPRO=" NOT LIKE ";
        value = value.replaceAll("NOT ", "").trim();
        value = value.replaceAll("%", "").trim();
        sEquals = "!=";
      }else{
        bLIKEPRO=" LIKE ";
      }
      value=TextUtility.replaceString(value,"*","%");
      fieldResult.add(value);
      if (bExactMatch == false) {

        tmpStr = sFieldName + bLIKEPRO + " ? ";
      }
      else {
        tmpStr = sFieldName + " " + sEquals + " ? " ;
      }
      sResult = sResult + tmpStr;
      exists++;

    }

    sResult = sResult+" ) ";
    mResult.put("SQL", sResult);
    mResult.put("?", TextUtility.formatInteger(exists));
    mResult.put("fieldResult", fieldResult);
    return mResult;
  }

  protected HashMap getCriteriaCombination(String sFieldName, String sFieldValue, boolean bExactMatch) {
    HashMap sResult = new HashMap();

    switch (DataSourceFactory.getDatabaseType()) {
      case DataSourceFactory.DB_MYSQL:
        sResult = getCriteriaCombinationMySQL(sFieldName, sFieldValue, bExactMatch);
        break;
      case DataSourceFactory.DB_INTERBASE:
        // Do Nothing.
        break;
      case DataSourceFactory.DB_POSTGRESQL:
        sResult = getCriteriaCombinationMySQL(sFieldName, sFieldValue, bExactMatch);
        break;
      case DataSourceFactory.DB_ORACLE:
        //sResult = getCriteriaCombinationMySQL(sFieldName, sFieldValue, bExactMatch);
          sResult = getCriteriaCombinationOracle(sFieldName, sFieldValue, bExactMatch);
        break;
      case DataSourceFactory.DB_MSSQL:
        // Same as Mysql version is fine
        sResult = getCriteriaCombinationMySQL(sFieldName, sFieldValue, bExactMatch);
        break;
      case DataSourceFactory.DB_SYBASE:
        // Do Nothing.
        break;
      case DataSourceFactory.DB_DB2:
        // Do Nothing.
        break;
    }

    return(sResult);
  }

  protected String getKeywordEscapeExactMatchs(String keyword, boolean prefixFlag) {
    String sResult = keyword;

    if (DataSourceFactory.DB_ORACLE == DataSourceFactory.getDatabaseType()) {
      sResult = sResult.toUpperCase();
    }

    sResult = TextUtility.replaceString(sResult, ESCAPE_CHAR, ESCAPE_CHAR + ESCAPE_CHAR);
//    sResult = TextUtility.replaceString(sResult, "\\", ESCAPE_CHAR+"\\");
    sResult = TextUtility.replaceString(sResult, "%", ESCAPE_CHAR+"%");
    sResult = TextUtility.replaceString(sResult, "_", ESCAPE_CHAR+"_");

    if (prefixFlag) {
      sResult = sResult + "%";
    } else {
      sResult = "%" + sResult + "%";
    }

    return sResult;
  }

  protected String getKeywordEscapeExactMatchs(String keyword) {
    return(getKeywordEscapeExactMatchs(keyword, false));
  }

  protected StringBuffer getSelectCountSQL(StringBuffer sbSQL) {
    StringBuffer sbResult = new StringBuffer("SELECT COUNT(*) FROM ");

    int idx = sbSQL.indexOf("FROM ");
    sbResult.append(sbSQL.substring(idx + 5));
    if (sbResult.indexOf("ORDER BY") >= 0) {
      sbResult = new StringBuffer(sbResult.substring(0, sbResult.indexOf("ORDER BY") - 1));
    }
    log.debug("COUNT SQL: " + sbResult);

    return(sbResult);
  }
  
  /**
   * When input sql contains 'distinct' keyword, the getSelectCountSQL() method would get the wrong total number.
   * Example for mysql: 
   *          SELECT DISTINCT A.DOCUMENT_TYPE, A.RECORD_STATUS FROM DMS_DOCUMENT A
   *          getSelectCountSQL method output: 
   *          SELECT COUNT(*) FROM DMS_DOCUMENT A
   *    The query result return would much bigger than we wanted, so over loading a new method handle these situation.
   *    The new output 'StringBuffer' like:  
   *          SELECT COUNT(DISTINCT CONCAT_WS(':', A.DOCUMENT_TYPE, A.RECORD_STATUS)) FROM DMS_DOCUMENT A
   * @param sbSQL
   * @return
   */
  protected StringBuffer getSelectCountSQLNew(StringBuffer sbSQL) {
    //if not mysql/SQL server/Oracle database, handle process by 'getSelectCountSQL()';
    if (DataSourceFactory.DB_MYSQL!=DataSourceFactory.getDatabaseType() && DataSourceFactory.DB_ORACLE!=DataSourceFactory.getDatabaseType() && DataSourceFactory.DB_MSSQL!=DataSourceFactory.getDatabaseType()) {
      return getSelectCountSQL(sbSQL);
    }
    
    StringBuffer sbResult = new StringBuffer();
    
    switch (DataSourceFactory.getDatabaseType()) {
      case DataSourceFactory.DB_MYSQL:
        sbResult.append("SELECT COUNT(");
        int idx_from     = sbSQL.indexOf("FROM ");
        int idx_select   = sbSQL.indexOf("SELECT ")+"SELECT ".length();
        int idx_distinct = sbSQL.indexOf(" DISTINCT ");
        if (idx_distinct>0) {
          sbResult.append("DISTINCT ");
          idx_select = idx_distinct + " DISTINCT ".length();
        }
        String fields = sbSQL.substring(idx_select, idx_from);
        if (fields.indexOf(",")!=-1) {
          sbResult.append("CONCAT_WS(':',"+fields+")");
        }else {
          sbResult.append(fields);
        }
        sbResult.append(") AS COUNTS ");
        sbResult.append(sbSQL.substring(idx_from));
        if (sbResult.indexOf("ORDER BY") >= 0) {
          sbResult = new StringBuffer(sbResult.substring(0, sbResult.indexOf("ORDER BY") - 1));
        }
        break;
      case DataSourceFactory.DB_INTERBASE:
        break;
      case DataSourceFactory.DB_POSTGRESQL:
        break;
      case DataSourceFactory.DB_ORACLE:
        sbResult.append("SELECT COUNT(*) FROM (");
        if (sbSQL.indexOf("ORDER BY") >= 0) {
          sbResult.append(sbSQL.substring(0, sbSQL.indexOf("ORDER BY") - 1));
        }else {
          sbResult.append(sbSQL);
        }
        sbResult.append(")");
        break;
      case DataSourceFactory.DB_MSSQL:
        sbResult.append("SELECT COUNT(*) FROM (");
        if (sbSQL.indexOf("ORDER BY") >= 0) {
          sbResult.append(sbSQL.substring(0, sbSQL.indexOf("ORDER BY") - 1));
        }else {
          sbResult.append(sbSQL);
        }
        sbResult.append(") AS AAA");
        break;
      case DataSourceFactory.DB_SYBASE:
        break;
      case DataSourceFactory.DB_DB2:
        break;
    }    
    log.debug("COUNT SQL: " + sbResult);
    return(sbResult);
  }

  protected String getFormattedKeyword(String sKeyword, String sSearchType) {
    String sResult = sKeyword.trim();
    if ("LIKE".equals(sSearchType.toUpperCase())) {
      //sResult = "%" + sKeyword + "%";
      sResult = getKeywordEscapeExactMatchs(sKeyword.trim());
    } else if ("LIKE ".equals(sSearchType.toUpperCase())) {
      sResult = getKeywordEscapeExactMatchs(sKeyword.trim(), true);
    }
    return(sResult);
  }

  
  public void finalize() throws Throwable {
    super.finalize();
  }

  protected StringBuffer getFormattedSQL(String sql) {
    StringBuffer result = null;
    //log.debug("before format: \n" + sql.toString());


    if (sql!=null) {
      StringBuffer tmpSql = new StringBuffer(sql);
      String ori = " LIKE ";
      int byPassingIndex = 0;
      while (tmpSql.indexOf(ori,byPassingIndex)!=-1) {
        byPassingIndex = tmpSql.indexOf(ori,byPassingIndex) + ori.length();
        int sqlLength = tmpSql.length();
        int andPos = tmpSql.indexOf("AND", byPassingIndex);
        int orPos = tmpSql.indexOf("OR", byPassingIndex);
        if (andPos != -1 && andPos < orPos) {
          orPos = -1;
        }
        if (orPos != -1 && orPos < andPos) {
          andPos = -1;
        }
        int subSymbolPos = tmpSql.indexOf("?", byPassingIndex);
        if (subSymbolPos!=-1) {
          if (andPos!=-1 && subSymbolPos<andPos) { //has AND statement behind
            tmpSql.insert(subSymbolPos+1, " ESCAPE '"+ESCAPE_CHAR+"' ");
            byPassingIndex = andPos;
          } else if (orPos!=-1 && subSymbolPos<orPos) { //has OR statement behind
            tmpSql.insert(subSymbolPos+1, " ESCAPE '"+ESCAPE_CHAR+"' ");
            byPassingIndex = orPos;
          } else if (subSymbolPos<sqlLength) {
            tmpSql.insert(subSymbolPos+1, " ESCAPE '"+ESCAPE_CHAR+"' ");
            byPassingIndex = sqlLength;
          }
        }
      }
      result = tmpSql;

    }

    //log.debug("Formatted SQL: \n" + result.toString());
    return result;

  }

  protected String getExactMatchKeyword(String sKeyword, boolean bExactMatch) {
    String sResult = sKeyword.trim();
    if (bExactMatch == false) {
      //sResult = "%" + sKeyword.trim() + "%";
      sResult = getKeywordEscapeExactMatchs(sKeyword.trim());
    }
    return(sResult);
  }

  protected String getSearchColumn(String str) {
    return getSearchColumn(str,"A");
  }

  protected String getSearchColumn(String str, String prefix) {
    String searchField = str;
    if (searchField.indexOf(".") < 0) {
      searchField = prefix + "." + searchField;
    }

    if (DataSourceFactory.DB_ORACLE == DataSourceFactory.getDatabaseType()) {
      searchField = "UPPER(" + searchField + ")";
    }

    return searchField;
  }

  protected String getIDFilterWhereClause(String prefixStr) {
    return this.getIDFilterWhereClause(prefixStr, true);
  }

  protected String getIDFilterWhereClause(String prefixStr, boolean hasPrefixAnd) {
    String result = "";
    String prefix = Utility.isEmpty(prefixStr) ? "" : (prefixStr + ".");
    if (SystemParameterFactory.getSystemParameterBoolean("system.multiple_segment")) {
      if (hasPrefixAnd) {
        result = " AND";
      }
      result += " (" + prefix + "ID >= " + SystemParameterFactory.getSystemParameter("system.id_segment_lower_bound") + " AND " + prefix + "ID <= " + SystemParameterFactory.getSystemParameter("system.id_segment_upper_bound") + ") ";
    }
    return result;
  }

  /**
   * Separates the IN clause.
   * To avoid oracle's limitation on expression limit, separate the X.FIELD IN (1,2,3.......) into blocks of
   * (X.FIELD IN (1,2) OR X.FIELD IN (3,4)) ....
   *
   * @param fieldName The field name
   * @param fieldValue The original comma separated value list
   * @return The separated in clause
   */
  protected String getInSQL ( String fieldName, String fieldValue ) {
    return getSeparatedList ( fieldName, fieldValue, "IN", "OR", 800 );
  }
  
 
  /**
   * Separates the NOT IN clause.
   * To avoid oracle's limitation on expression limit, separate the X.FIELD NOT IN (1,2,3.......) into blocks of
   * (X.FIELD IN (1,2) AND X.FIELD IN (3,4)) ....
   *
   * @param fieldName The field name
   * @param fieldValue The original comma separated value list
   * @return The separated not in clause
   */
  protected String getNotInSQL ( String fieldName, String fieldValue ) {
    return getSeparatedList ( fieldName, fieldValue, "NOT IN", "AND", 800 );
  }
  
 
  protected String getSeparatedList ( String fieldName, String fieldValue, String compareOperand, String logicalOperand, int limit ) {
    String separator = ",";
    if(fieldValue.indexOf(separator)==-1 || fieldValue.length()<limit) {
      return fieldName + " "+compareOperand+" (" + fieldValue + ") ";
    }else{
      String val[] = TextUtility.splitString(fieldValue, separator);
      List valList = java.util.Arrays.asList(val);
      if(valList.size() < limit) {
        return fieldName + " "+compareOperand+" (" + fieldValue + ") ";
      }else{
        StringBuffer sql = new StringBuffer();
        sql.append("(");
        for(int i = 0 ; i < valList.size() ; i=i+limit){
          List subList = valList.subList(i, Math.min(i+limit, valList.size()));
          sql.append( fieldName+" "+compareOperand+" ("+TextUtility.join(separator, subList.iterator())+") " );
          if((i+limit)<val.length){
            sql.append( " "+logicalOperand+" " );
          }
        }
        sql.append(")");
        return sql.toString();
      }
    }
  }

  protected void postInsert(Integer primaryID) throws ApplicationException {
  }

  protected void postUpdate(Integer primaryID) throws ApplicationException {
  }

  protected void postDelete(Integer primaryID) throws ApplicationException {
  }

  // Protected methods which can be implemented by the sub-classes.
  protected abstract AbstractBaseObject getByID(Integer id) throws ApplicationException;

  protected abstract List getList(com.dcivision.framework.web.AbstractSearchForm searchForm) throws ApplicationException;

  protected abstract List getList() throws ApplicationException;

  protected abstract AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException;

  protected abstract AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException;

  protected abstract AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException;

  protected abstract void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException;

  protected abstract void initDBSetting();

  protected abstract void validateInsert(AbstractBaseObject obj) throws ApplicationException;

  protected abstract void validateUpdate(AbstractBaseObject obj) throws ApplicationException;

  protected abstract void validateDelete(AbstractBaseObject obj) throws ApplicationException;

  public Log getLog() {
    return log;
  }

  public void setLog(Log log) {
    this.log = log;
  }

}
